### 上传表和更新表：关于保底小时数：
import pandas as pd
from mysql.connector import Error
from utils import *

def upload_table(df, private=True):
    '''
    仅适用于初次上传：
    '''
    try:
        # 连接数据库：
        conn = get_mysql_conn(MySQLTableConfig.backup, private=private)
        cursor = conn.cursor()

        # 创建新的数据表：
        table_name = 'pvsyst_gen_hours'
        type_mapping = {
            'int64': 'INT',
            'float64': 'FLOAT',
            'object': 'VARCHAR(255)',
            'datetime64[ns]': 'DATETIME'
        }

        # 生成列定义
        df.columns = df.columns.str.replace(r'[^\w]', '_', regex=True) # / 换成下划线
        columns = []
        for col, dtype in zip(df.columns, df.dtypes):
            mysql_type = type_mapping.get(str(dtype), 'VARCHAR(255)')
            columns.append(f"`{col}` {mysql_type}")

        create_table_sql = f"""
               CREATE TABLE IF NOT EXISTS {table_name} (
                   {', '.join(columns)}
               )
               """
        cursor.execute(create_table_sql)
        print(f"表 '{table_name}' 创建成功")

        # 4. 插入数据（批量插入）
        # 将 DataFrame 转换为元组列表
        data = [tuple(row) for row in df.values]

        # 生成占位符 (如 %s, %s, ...)
        placeholders = ', '.join(['%s'] * len(df.columns))

        # 将原表数据删除：
        cursor.execute(f"TRUNCATE TABLE {table_name}")  # 快速清空表

        insert_sql = f"""
               INSERT INTO {table_name} 
               ({', '.join([f'`{col}`' for col in df.columns])})
               VALUES ({placeholders})
               """

        cursor.executemany(insert_sql, data)
        conn.commit()
        print(f"成功插入 {len(df)} 条数据")
    except Error as e:
        print(f"操作失败，错误信息: {e}")
    finally:
        if 'conn' in locals() and conn.open:  # 使用 open 属性
            cursor.close()
            conn.close()
            print("数据库连接已关闭")

def update_table(df, private=True):
    '''
    假设要更新这个表：
    '''
    pass


if __name__ == '__main__':
    df = pd.read_excel(r"D:\work_files\projcet\generation_hour\phour_easy_report\网格化采样点\all_grid_sites.xlsx",dtype={'adcode':str})
    # upload_table(df)
    conn = get_mysql_conn(MySQLTableConfig.backup, private=private)
    cursor = conn.cursor()

    # 创建新的数据表：
    # table_name = 'pvsyst_gen_hours'
    table_name = 'girded_sites'
    type_mapping = {
        'int64': 'INT',
        'float64': 'FLOAT',
        'object': 'VARCHAR(255)',
        'datetime64[ns]': 'DATETIME'
    }

    # 修改后的列定义逻辑
    columns = []
    for col, dtype in zip(df.columns, df.dtypes):
        if col == 'adcode':
            mysql_type = 'VARCHAR(12) CHARACTER SET latin1'  # 优化存储
        elif str(dtype) == 'int64':
            mysql_type = 'BIGINT'  # 避免其他数值列溢出
        else:
            mysql_type = type_mapping.get(str(dtype), 'VARCHAR(255)')
        columns.append(f"`{col}` {mysql_type}")

    # 先修改现有表结构（如果存在）
    cursor.execute(f"ALTER TABLE {table_name} MODIFY adcode VARCHAR(12);")

    # 生成列定义
    # columns = []
    # for col, dtype in zip(df.columns, df.dtypes):
    #     mysql_type = type_mapping.get(str(dtype), 'VARCHAR(255)')
    #     columns.append(f"`{col}` {mysql_type}")
    #
    # create_table_sql = f"""
    #                CREATE TABLE IF NOT EXISTS {table_name} (
    #                    {', '.join(columns)}
    #                )
    #                """
    # cursor.execute(create_table_sql)
    print(f"表 '{table_name}' 创建成功")

    # 4. 插入数据（批量插入）
    # 将 DataFrame 转换为元组列表
    data = [tuple(row) for row in df.values]

    # 生成占位符 (如 %s, %s, ...)
    placeholders = ', '.join(['%s'] * len(df.columns))

    # 将原表数据删除：
    cursor.execute(f"TRUNCATE TABLE {table_name}")  # 快速清空表

    insert_sql = f"""
                   INSERT INTO {table_name} 
                   ({', '.join([f'`{col}`' for col in df.columns])})
                   VALUES ({placeholders})
                   """

    cursor.executemany(insert_sql, data)
    conn.commit()
    print(f"成功插入 {len(df)} 条数据")
